package com.css.zfzx.fxpg.common.init;

import com.css.bpm.platform.utils.UUIDGenerator;
import com.css.zfzx.fxpg.modules.casulaty.estimate.repository.entity.FxCasulatyEstimateEntity;
import com.css.zfzx.fxpg.modules.casulaty.probability.repository.entity.FxCasulatyProbabilityEntity;
import com.css.zfzx.fxpg.modules.economicloss.middle.repository.entity.FxEconomyLossMiddleEntity;
import com.css.zfzx.fxpg.modules.economicloss.probability.repository.entity.FxEconomyLossProbabilityComputeEntity;
import com.css.zfzx.fxpg.modules.middle.repository.entity.FxMiddleResultEntity;
import com.css.zfzx.fxpg.modules.percent.casualty.repository.entity.FxCasualtyPercentEntity;
import com.css.zfzx.fxpg.modules.percent.economicloss.repository.entity.FxEconomicLossEntity;
import com.css.zfzx.fxpg.modules.risk.repository.entity.FxRiskResultEntity;
import com.css.zfzx.fxpg.modules.vulnerability.danger.repository.entity.FxVulnerabilityDangerEntity;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 初始化数据
 * 解决上万条数据插入慢的问题
 *
 * @Author leon
 * @Date: 2021/2/2 10:24 上午
 */
public class Initial {
    /**
     * 数据源
     * 10.19.41.111
     * 10.7.208.196
     */
    private final static String url = "jdbc:mysql://10.7.208.196:3306/base?useUnicode=true&characterEncoding=UTF-8";
    private final static String name = "root";
    private final static String pwd = "1234567890";
    private static Connection conn;
    private static PreparedStatement ps;

    /**
     * 获取数据库连接
     * SQL语句
     */
    private static Connection getConnection() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection(url, name, pwd);
        return conn;
    }

    public static void main(String[] args) {

        // psBatch 时间统计 - 开始
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String startTime = sdf.format(new Date());
        System.out.println("psBatch 开始时间为：" + startTime);
        System.out.println("psBatch 开始执行...");

        List<FxCasulatyEstimateEntity> list = new ArrayList<>();
        FxCasulatyEstimateEntity entity = new FxCasulatyEstimateEntity();
        entity.setProvince("北京市");
        entity.setCity("北京市");
        entity.setArea("朝阳区");
        entity.setIntensity("6");
        entity.setCasulaty("重伤");
        entity.setPeriod("夜间");
        entity.setDamageDegree("1");
        list.add(entity);
        // 使用PreparedStatement批量插入
        int idx = sqlBatchCE(list);

        // 统计时间 - 结束
        System.out.println("psBatch 执行完成，共插入" + idx + "条数据");
        String endTime = sdf.format(new Date());
        System.out.println("psBatch 结束时间为：" + endTime);
    }

    /**
     * sql 批量插入: 伤亡估计
     *
     * @return
     */
    public static int sqlBatchCE(List<FxCasulatyEstimateEntity> list) {
        int count = 0;
        try {
            /**
             * 获取数据库连接
             */
            conn = getConnection();

            // SQL: id、省、市、区、烈度、人员伤亡、时间段、破坏程度
            StringBuffer sql = new StringBuffer("insert into fx_casulaty_estimate (id, province, city, area, intensity, casulaty, period, damage_degree) "
                    + "values ");
            // 上万条数据
            for (FxCasulatyEstimateEntity entity : list) {
                count++;
                StringBuffer suffix = new StringBuffer();
                String id = UUIDGenerator.getUUID();
                String province = entity.getProvince();
                String city = entity.getCity();
                String area = entity.getArea();
                String intensity = entity.getIntensity();
                String casulaty = entity.getCasulaty();
                String period = entity.getPeriod();
                String damageDegree = entity.getDamageDegree();
                suffix.append(sql).append("('" + id + "','" + province + "','" + city + "','" + area + "','" + intensity + "','" + casulaty + "','" + period + "','" + damageDegree + "') ");

                ps = conn.prepareStatement(suffix.toString());
                ps.executeUpdate(suffix.toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("第" + count + "条数据插入");
        } finally {
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return count;
    }

    /**
     * 重置单价
     *
     * @param list
     * @return
     */
    public static int sqlBatchEL(List<FxEconomicLossEntity> list) {
        int count = 0;
        try {
            /**
             * 获取数据库连接
             */
            conn = getConnection();

            // SQL: id、省、市、区、结构类型
            StringBuffer sql = new StringBuffer("insert into fx_economic_loss (id, province, city, area, structure_type) "
                    + "values ");
            // 上万条数据
            for (FxEconomicLossEntity entity : list) {
                count++;
                StringBuffer suffix = new StringBuffer();
                String id = UUIDGenerator.getUUID();
                String province = entity.getProvince();
                String city = entity.getCity();
                String area = entity.getArea();
                String structureType = entity.getStructureType();

                suffix.append(sql).append("('" + id + "','" + province + "','" + city + "','" + area + "','" + structureType + "') ");

                ps = conn.prepareStatement(suffix.toString());
                ps.executeUpdate(suffix.toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("第" + count + "条数据插入");
        } finally {
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return count;
    }


    /**
     * 人员伤亡损失比
     *
     * @param list
     * @return
     */
    public static int sqlBatchCP(List<FxCasualtyPercentEntity> list) {
        int count = 0;
        try {
            /**
             * 获取数据库连接
             */
            conn = getConnection();

            // SQL: id、省、市、区、结构类型
            StringBuffer sql = new StringBuffer("insert into fx_casualty_percent (id, province, city, area, damage_degree) "
                    + "values ");
            // 上万条数据
            for (FxCasualtyPercentEntity entity : list) {
                count++;
                StringBuffer suffix = new StringBuffer();
                String id = UUIDGenerator.getUUID();
                String province = entity.getProvince();
                String city = entity.getCity();
                String area = entity.getArea();
                String damageDegree = entity.getDamageDegree();

                suffix.append(sql).append("('" + id + "','" + province + "','" + city + "','" + area + "','" + damageDegree + "') ");

                ps = conn.prepareStatement(suffix.toString());
                ps.executeUpdate(suffix.toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("第" + count + "条数据插入");
        } finally {
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return count;
    }


    /**
     * 人员伤亡概率
     *
     * @param list
     * @return
     */
    public static int sqlBatchCPro(List<FxCasulatyProbabilityEntity> list) {
        int count = 0;
        try {
            /**
             * 获取数据库连接
             */
            conn = getConnection();

            // SQL: id、省、市、区、人员伤亡、时间段、破坏程度
            StringBuffer sql = new StringBuffer("insert into fx_casulaty_probability (id, province, city, area, casulaty, period, damage_degree) "
                    + "values ");
            // 上万条数据
            for (FxCasulatyProbabilityEntity entity : list) {
                count++;
                StringBuffer suffix = new StringBuffer();
                String id = UUIDGenerator.getUUID();
                String province = entity.getProvince();
                String city = entity.getCity();
                String area = entity.getArea();
                String casulaty = entity.getCasulaty();
                String period = entity.getPeriod();
                String damageDegree = entity.getDamageDegree();
                suffix.append(sql).append("('" + id + "','" + province + "','" + city + "','" + area + "','" + casulaty + "','" + period + "','" + damageDegree + "') ");

                ps = conn.prepareStatement(suffix.toString());
                ps.executeUpdate(suffix.toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("第" + count + "条数据插入");
        } finally {
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return count;
    }

    /**
     * 房屋抗震能力(中间结果)
     *
     * @param list
     * @return
     */
    public static int sqlBatchMR(List<FxMiddleResultEntity> list) {
        int count = 0;
        try {
            /**
             * 获取数据库连接
             */
            conn = getConnection();

            // SQL: id、省、市、区、地震动强度、破坏程度
            StringBuffer sql = new StringBuffer("insert into fx_middle_result (id, province, city, area, earthquake_intensity, damage_degree) "
                    + "values ");
            // 上万条数据
            for (FxMiddleResultEntity entity : list) {
                count++;
                StringBuffer suffix = new StringBuffer();
                String id = UUIDGenerator.getUUID();
                String province = entity.getProvince();
                String city = entity.getCity();
                String area = entity.getArea();
                String earthquakeIntensity = entity.getEarthquakeIntensity();
                String damageDegree = entity.getDamageDegree();
                suffix.append(sql).append("('" + id + "','" + province + "','" + city + "','" + area + "','" + earthquakeIntensity + "','" + damageDegree + "') ");

                ps = conn.prepareStatement(suffix.toString());
                ps.executeUpdate(suffix.toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("第" + count + "条数据插入");
        } finally {
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return count;
    }

    /**
     * 经济损失估计
     *
     * @param list
     * @return
     */
    public static int sqlBatchELM(List<FxEconomyLossMiddleEntity> list) {
        int count = 0;
        try {
            /**
             * 获取数据库连接
             */
            conn = getConnection();

            // SQL: id、省、市、区、地震动强度、破坏程度
            StringBuffer sql = new StringBuffer("insert into fx_economy_loss_middle (id, province, city, area, earthquake_intensity, damage_degree) "
                    + "values ");
            // 上万条数据
            for (FxEconomyLossMiddleEntity entity : list) {
                count++;
                StringBuffer suffix = new StringBuffer();
                String id = UUIDGenerator.getUUID();
                String province = entity.getProvince();
                String city = entity.getCity();
                String area = entity.getArea();
                String earthquakeIntensity = entity.getEarthquakeIntensity();
                String damageDegree = entity.getDamageDegree();
                suffix.append(sql).append("('" + id + "','" + province + "','" + city + "','" + area + "','" + earthquakeIntensity + "','" + damageDegree + "') ");

                ps = conn.prepareStatement(suffix.toString());
                ps.executeUpdate(suffix.toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("第" + count + "条数据插入");
        } finally {
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return count;
    }


    /**
     * 经济损失概率
     *
     * @param list
     * @return
     */
    public static int sqlBatchELPC(List<FxEconomyLossProbabilityComputeEntity> list) {
        int count = 0;
        try {
            /**
             * 获取数据库连接
             */
            conn = getConnection();

            // SQL: id、省、市、区、地震动强度、破坏程度
            StringBuffer sql = new StringBuffer("insert into fx_economy_loss_probability_compute (id, province, city, area, damage_degree) "
                    + "values ");
            // 上万条数据
            for (FxEconomyLossProbabilityComputeEntity entity : list) {
                count++;
                StringBuffer suffix = new StringBuffer();
                String id = UUIDGenerator.getUUID();
                String province = entity.getProvince();
                String city = entity.getCity();
                String area = entity.getArea();
                String damageDegree = entity.getDamageDegree();
                suffix.append(sql).append("('" + id + "','" + province + "','" + city + "','" + area + "','" + damageDegree + "') ");

                ps = conn.prepareStatement(suffix.toString());
                ps.executeUpdate(suffix.toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("第" + count + "条数据插入");
        } finally {
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return count;
    }


    /**
     * 区划危险性数据
     *
     * @param list
     * @return
     */
    public static int sqlBatchDanger(List<FxVulnerabilityDangerEntity> list) {
        int count = 0;
        try {
            /**
             * 获取数据库连接
             */
            conn = getConnection();

            // SQL: id、省、市、区、地震动强度、破坏程度
            StringBuffer sql = new StringBuffer("insert into fx_vulnerability_danger (id, province, city, area) "
                    + "values ");
            // 上万条数据
            for (FxVulnerabilityDangerEntity entity : list) {
                count++;
                StringBuffer suffix = new StringBuffer();
                String id = UUIDGenerator.getUUID();
                String province = entity.getProvince();
                String city = entity.getCity();
                String area = entity.getArea();
                suffix.append(sql).append("('" + id + "','" + province + "','" + city + "','" + area + "') ");

                ps = conn.prepareStatement(suffix.toString());
                ps.executeUpdate(suffix.toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("第" + count + "条数据插入");
        } finally {
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return count;
    }

    /**
     * 风险结果
     *
     * @param list
     * @return
     */
    public static int sqlBatchRisk(List<FxRiskResultEntity> list) {
        int count = 0;
        try {
            /**
             * 获取数据库连接
             */
            conn = getConnection();

            // SQL: id、省、市、区、地震动强度、破坏程度
            StringBuffer sql = new StringBuffer("insert into fx_risk_result (id, province, city, area) "
                    + "values ");
            // 上万条数据
            for (FxRiskResultEntity entity : list) {
                count++;
                StringBuffer suffix = new StringBuffer();
                String id = UUIDGenerator.getUUID();
                String province = entity.getProvince();
                String city = entity.getCity();
                String area = entity.getArea();
                suffix.append(sql).append("('" + id + "','" + province + "','" + city + "','" + area + "') ");

                ps = conn.prepareStatement(suffix.toString());
                ps.executeUpdate(suffix.toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("第" + count + "条数据插入");
        } finally {
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return count;
    }
}
